const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const session = require('express-session');
const multer = require('multer');
const path = require('path');
const fs = require('fs');

// Express应用初始化
const app = express();
const port = 3009;

/* ==============================================
   中间件配置
   ============================================== */
// 静态文件服务配置
app.use(express.static('public'));
app.use('/uploads', express.static('uploads'));
app.use('/bootstrap', express.static(path.join(__dirname, 'node_modules/bootstrap/dist')));
// 配置Chart.js和xlsx的本地静态路径
app.use('/chart.js', express.static(path.join(__dirname, 'node_modules/chart.js/dist')));
app.use('/xlsx', express.static(path.join(__dirname, 'node_modules/xlsx/dist')));

// 请求体解析中间件
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// 会话配置
app.use(session({
    secret: 'your-secret-key',
    resave: false,
    saveUninitialized: false,
    cookie: { secure: false } // 生产环境应该设置为true并启用HTTPS
}));

/* ==============================================
   数据库初始化
   ============================================== */
const db = new sqlite3.Database('asset-management.db');

// 数据表创建和测试数据插入
db.serialize(() => {
    // 用户表结构
    db.run(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'user',
            created_at DATETIME DEFAULT (datetime('now', '+8 hours'))
        )
    `);

      // 加密测试用户密码
    const adminPassword = bcrypt.hashSync('123456', 10);
    const userPassword = bcrypt.hashSync('123456', 10);
    
    // 测试用户数据
    db.run(`
        INSERT OR IGNORE INTO users (username, password, role) VALUES 
        ('admin', ?, 'admin'),
        ('user1', ?, 'user')
    `, [adminPassword, userPassword]);

    // 资产报告表结构
    db.run(`
        CREATE TABLE IF NOT EXISTS reports (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            asset_number TEXT ,
            asset_name TEXT NOT NULL,
            category_main TEXT NOT NULL,
            category_sub TEXT NOT NULL,
            unit TEXT NOT NULL,
            value DECIMAL(10,2) NOT NULL,
            status TEXT NOT NULL,
            department TEXT NOT NULL,
            location TEXT NOT NULL,
            service_life INTEGER,
            depreciation_rate DECIMAL(5,2),
            purchase_date TEXT,
            manufacturer TEXT,
            model TEXT,
            description TEXT,
            images TEXT,
            created_at DATETIME DEFAULT (datetime('now', '+8 hours')),
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    `);
});

/* ==============================================
   文件上传配置
   ============================================== */
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        const uploadDir = 'uploads';
        if (!fs.existsSync(uploadDir)) {
            fs.mkdirSync(uploadDir);
        }
        cb(null, uploadDir);
    },
    filename: (req, file, cb) => {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        cb(null, uniqueSuffix + path.extname(file.originalname));
    }
});

const upload = multer({
    storage: storage,
    limits: {
        fileSize: 1024 * 1024, // 1MB
        files: 3 // 最多3个文件
    },
    fileFilter: (req, file, cb) => {
        const allowedTypes = ['image/jpeg', 'image/png', 'image/gif'];
        if (allowedTypes.includes(file.mimetype)) {
            cb(null, true);
        } else {
            cb(new Error('只允许上传图片文件'));
        }
    }
});

/* ==============================================
   自定义中间件
   ============================================== */
// 登录检查中间件
function requireLogin(req, res, next) {
    if (!req.session.user) {
        return res.redirect('/login');
    }
    next();
}

// 管理员权限检查中间件
function requireAdmin(req, res, next) {
    if (!req.session.user || req.session.user.role !== 'admin') {
        return res.status(403).send('无权访问');
    }
    next();
}

/* ==============================================
   页面路由
   ============================================== */
// 首页路由
app.get('/', (req, res) => {
    if (req.session.user) {
        return res.redirect('/dashboard');
    }
    res.sendFile(path.join(__dirname, 'public/index.html'));
});

// 登录页面路由
app.get('/login', (req, res) => {
    if (req.session.user) {
        return res.redirect('/dashboard');
    }
    res.sendFile(path.join(__dirname, 'public/index.html'));
});

// 仪表盘路由
app.get('/dashboard', requireLogin, (req, res) => {
    res.sendFile(path.join(__dirname, 'public/dashboard.html'));
});

// 资产表单页面路由
app.get('/report', requireLogin, (req, res) => {
    res.sendFile(path.join(__dirname, 'public/report.html'));
});

// 用户管理页面路由 (仅管理员)
app.get('/admin/users', requireLogin, requireAdmin, (req, res) => {
    res.sendFile(path.join(__dirname, 'public/users.html'));
});

/* ==============================================
   API路由 - 认证相关
   ============================================== */
// 登录处理 - 修改后的版本
app.post('/login', (req, res) => {
    const { username, password } = req.body;

    db.get('SELECT * FROM users WHERE username = ?', [username], (err, user) => {
        if (err) {
            return res.status(500).send('数据库错误');
        }

        if (!user) {
            return res.status(401).send('用户名或密码错误');
        }

        // 使用bcrypt比较密码
        bcrypt.compare(password, user.password, (err, isMatch) => {
            if (err) {
                return res.status(500).send('服务器错误');
            }

            if (!isMatch) {
                return res.status(401).send('用户名或密码错误');
            }

            req.session.user = {
                id: user.id,
                username: user.username,
                role: user.role
            };

            res.redirect('/dashboard');
        });
    });
});

// 添加新用户 - 修改后的版本
app.post('/api/users', requireLogin, requireAdmin, (req, res) => {
    const { username, password, role } = req.body;

    if (!username || !password || !role) {
        return res.status(400).json({ error: '用户名、密码和角色都是必填的' });
    }

    // 对密码进行加密
    bcrypt.hash(password, 10, (err, hashedPassword) => {
        if (err) {
            return res.status(500).json({ error: '密码加密失败' });
        }

        db.run(
            'INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
            [username, hashedPassword, role],
            function (err) {
                if (err) {
                    return res.status(500).json({ error: '用户名已存在或数据库错误' });
                }
                res.json({ success: true, userId: this.lastID });
            }
        );
    });
});

// 更新用户信息 - 修改后的版本
app.put('/api/users/:id', requireLogin, requireAdmin, (req, res) => {
    const { username, password, role } = req.body;
    const userId = req.params.id;

    if (!username || !role) {
        return res.status(400).json({ error: '用户名和角色是必填的' });
    }

    // 如果有新密码则更新密码，否则保留原密码
    if (password) {
        bcrypt.hash(password, 10, (err, hashedPassword) => {
            if (err) {
                return res.status(500).json({ error: '密码加密失败' });
            }

            db.run(
                'UPDATE users SET username = ?, password = ?, role = ? WHERE id = ?',
                [username, hashedPassword, role, userId],
                function (err) {
                    if (err) return res.status(500).json({ error: '数据库错误' });
                    res.json({ success: true });
                }
            );
        });
    } else {
        db.run(
            'UPDATE users SET username = ?, role = ? WHERE id = ?',
            [username, role, userId],
            function (err) {
                if (err) return res.status(500).json({ error: '数据库错误' });
                res.json({ success: true });
            }
        );
    }
});

// 登出处理
app.get('/logout', (req, res) => {
    req.session.destroy();
    res.redirect('/');
});

/* ==============================================
   API路由 - 用户管理相关
   ============================================== */
// 获取所有用户 (仅管理员)
app.get('/api/users', requireLogin, requireAdmin, (req, res) => {
    db.all('SELECT id, username, role, created_at FROM users ORDER BY created_at DESC', [], (err, users) => {
        if (err) {
            return res.status(500).json({ error: '数据库错误' });
        }
        res.json(users);
    });
});

// 获取单个用户信息 (仅管理员)
app.get('/api/users/:id', requireLogin, requireAdmin, (req, res) => {
    const userId = req.params.id;

    db.get('SELECT id, username, role FROM users WHERE id = ?', [userId], (err, user) => {
        if (err) {
            return res.status(500).json({ error: '数据库错误' });
        }
        if (!user) {
            return res.status(404).json({ error: '用户不存在' });
        }
        res.json(user);
    });
});


// 删除用户 (仅管理员)
app.delete('/api/users/:id', requireLogin, requireAdmin, (req, res) => {
    const userId = req.params.id;

    db.run(
        'DELETE FROM users WHERE id = ?',
        [userId],
        function (err) {
            if (err) return res.status(500).json({ error: '数据库错误' });
            res.json({ success: true });
        }
    );
});
// 后端API - 导出路由
app.get('/api/reports/export', requireLogin, async (req, res) => {
    try {
        const user = req.session.user;
        let { start_date, end_date, status, department } = req.query;
        
        // 解码URL编码的参数
        if (status) status = decodeURIComponent(status);
        if (department) department = decodeURIComponent(department);

        // 构建基础查询
        let baseQuery = `
            SELECT r.*, u.username 
            FROM reports r 
            JOIN users u ON r.user_id = u.id
        `;

        const conditions = [];
        const params = [];

        // 添加权限条件（非管理员只能查看自己的报告）
        if (user.role !== 'admin') {
            conditions.push('r.user_id = ?');
            params.push(user.id);
        }

        // 添加日期范围条件
        if (start_date && end_date) {
            conditions.push("date(r.created_at) BETWEEN date(?) AND date(?)");
            params.push(start_date, end_date);
        }

        // 添加状态条件
        if (status && status !== '全部') {
            conditions.push('r.status = ?');
            params.push(status);
        }

        // 部门模糊查询
        if (department && department.trim() !== '') {
            conditions.push('r.department LIKE ?');
            params.push(`%${department.trim()}%`);
        }

        // 组合所有条件
        if (conditions.length > 0) {
            baseQuery += ' WHERE ' + conditions.join(' AND ');
        }

        // 获取所有数据（不分页）
        const dataQuery = baseQuery + ' ORDER BY r.created_at DESC';

        const reports = await new Promise((resolve, reject) => {
            db.all(dataQuery, params, (err, rows) => {
                if (err) {
                    console.error('Export Query Error:', err);
                    reject(err);
                } else {
                    resolve(rows || []);
                }
            });
        });

        // 处理报告数据
        const processedReports = reports.map(report => {
            return {
                ...report,
                images: report.images 
                    ? report.images.split(',').map(img => `/uploads/${img.trim()}`) 
                    : [],
                category_main: report.category_main || '未分类',
                status: report.status || '正常',
                description: report.description || '',
                department: report.department || '未知部门'
            };
        });

        res.json({
            success: true,
            data: processedReports
        });

    } catch (error) {
        console.error('导出报告错误:', error);
        res.status(500).json({
            success: false,
            error: '服务器内部错误',
            message: error.message
        });
    }
});
/* ==============================================
   API路由 - 资产报告相关
   ============================================== */

// 替换原有的 /api/reports 路由为以下版本（确保只有一个定义）
app.get('/api/reports', requireLogin, async (req, res) => {
    try {
        const user = req.session.user;
        let { start_date, end_date, status, department, page = 1, pageSize = 50 } = req.query;
        
        // 参数验证和转换
        page = parseInt(page, 10) || 1;
        pageSize = parseInt(pageSize, 10) || 50;
        page = Math.max(1, page);
        pageSize = Math.min(Math.max(1, pageSize), 100);
        
        const offset = (page - 1) * pageSize;

        // 解码URL编码的参数
        if (status) status = decodeURIComponent(status);
        if (department) department = decodeURIComponent(department);

        // 构建基础查询
        let baseQuery = `
            SELECT r.*, u.username 
            FROM reports r 
            JOIN users u ON r.user_id = u.id
        `;

        let countQuery = `SELECT COUNT(*) as total FROM reports r`;
        const conditions = [];
        const params = [];

        // 添加权限条件（非管理员只能查看自己的报告）
        if (user.role !== 'admin') {
            conditions.push('r.user_id = ?');
            params.push(user.id);
        }

        // 添加日期范围条件
        if (start_date && end_date) {
            conditions.push("date(r.created_at) BETWEEN date(?) AND date(?)");
            params.push(start_date, end_date);
        }

        // 添加状态条件
        if (status && status !== '全部') {
            conditions.push('r.status = ?');
            params.push(status);
        }

        // 部门模糊查询
        if (department && department.trim() !== '') {
            conditions.push('r.department LIKE ?');
            params.push(`%${department.trim()}%`);
        }

        // 组合所有条件
        if (conditions.length > 0) {
            const whereClause = ' WHERE ' + conditions.join(' AND ');
            baseQuery += whereClause;
            countQuery += whereClause;
        }

        // 获取总数
        const countResult = await new Promise((resolve, reject) => {
            db.get(countQuery, params, (err, result) => {
                if (err) {
                    console.error('Count Query Error:', err);
                    reject(err);
                } else {
                    console.log('Count Result:', result);
                    resolve(result);
                }
            });
        });

        const total = countResult ? countResult.total : 0;
        const totalPages = Math.ceil(total / pageSize);

        // 获取分页数据
        const dataQuery = baseQuery + ' ORDER BY r.created_at DESC LIMIT ? OFFSET ?';
        const dataParams = [...params, pageSize, offset];

        const reports = await new Promise((resolve, reject) => {
            db.all(dataQuery, dataParams, (err, rows) => {
                if (err) {
                    console.error('Data Query Error:', err);
                    reject(err);
                } else {
                    resolve(rows || []);
                }
            });
        });

        // 处理报告数据
        const processedReports = reports.map(report => {
            return {
                ...report,
                images: report.images 
                    ? report.images.split(',').map(img => `/uploads/${img.trim()}`) 
                    : [],
                category_main: report.category_main || '未分类',
                status: report.status || '正常',
                description: report.description || '',
                department: report.department || '未知部门'
            };
        });

        // 返回响应 - 确保结构正确
        res.json({
            success: true,
            pagination: {
                total: Number(total),
                totalPages,
                currentPage: page,
                pageSize
            },
            data: processedReports
        });

    } catch (error) {
        console.error('获取报告列表错误:', error);
        res.status(500).json({
            success: false,
            error: '服务器内部错误',
            message: error.message
        });
    }
});

// 获取单个报告详情
app.get('/api/reports/:id', requireLogin, (req, res) => {
    const reportId = req.params.id;
    const user = req.session.user;

    const query = `
        SELECT r.*, u.username 
        FROM reports r 
        JOIN users u ON r.user_id = u.id 
        WHERE r.id = ?
    `;

    db.get(query, [reportId], (err, report) => {
        if (err) {
            console.error('数据库查询错误:', err);
            return res.status(500).json({ error: '数据库错误' });
        }

        if (!report) {
            return res.status(404).json({ error: '报告不存在' });
        }

        // 检查权限
        if (user.role !== 'admin' && report.user_id !== user.id) {
            return res.status(403).json({ error: '无权访问此报告' });
        }

        // 处理图片路径
        let images = [];
        if (report.images) {
            images = report.images.split(',').map(img => `/uploads/${img}`);
        }

        res.json({
            id: report.id,
            asset_number: report.asset_number,
            asset_name: report.asset_name,
            category_main: report.category_main,
            category_sub: report.category_sub,
            unit: report.unit,
            value: report.value,
            status: report.status,
            department: report.department,
            location: report.location,
            service_life: report.service_life,
            depreciation_rate: report.depreciation_rate,
            purchase_date: report.purchase_date,
            manufacturer: report.manufacturer,
            model: report.model,
            description: report.description,
            images: images,
            username: report.username,
            created_at: report.created_at
        });
    });
});
// 提交新报告
app.post('/api/reports', requireLogin, upload.array('images', 3), (req, res) => {
    const {
        asset_name,
        category_main,
        category_sub,
        unit,
        value,
        status,
        department,
        location,
        service_life,
        depreciation_rate,
        purchase_date,
        manufacturer,
        model,
        description
    } = req.body;
    
    const user = req.session.user;

    // 验证输入（移除了asset_number的检查）
    if (!asset_name || !category_main || !unit || !value || !department || !location) {
        return res.status(400).json({ error: '所有必填字段都是必填的' });
    }

    // 处理上传的图片
    let images = '';
    if (req.files && req.files.length > 0) {
        images = req.files.map(file => file.filename).join(',');
    }

    // 插入数据库（移除了asset_number字段）
    db.run(
        `INSERT INTO reports (
            user_id, asset_name, category_main, category_sub, 
            unit, value, status, department, location, 
            service_life, depreciation_rate, purchase_date, 
            manufacturer, model, description, images
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
            user.id,
            asset_name,
            category_main,
            category_sub || '',
            unit,
            value,
            status || '正常',
            department,
            location,
            service_life || null,
            depreciation_rate || null,
            purchase_date || '',
            manufacturer || '',
            model || '',
            description || '',
            images
        ],
        function (err) {
            if (err) {
                console.error('数据库错误:', err);
                return res.status(500).json({ error: '数据库错误' });
            }

            res.json({ success: true, reportId: this.lastID });
        }
    );
});



// 更新报告信息（增加图片上传功能）
app.put('/api/reports/:id', requireLogin, upload.array('images', 3), (req, res) => {
    const reportId = req.params.id;
    const user = req.session.user;
    const {
        asset_number,
        asset_name,
        category_main,
        category_sub,
        unit,
        value,
        status,
        department,
        location,
        service_life,
        depreciation_rate,
        purchase_date,
        manufacturer,
        model,
        description,
        existing_images // 前端传递的已存在图片列表，逗号分隔
    } = req.body;

    // 首先检查报告是否存在
    db.get('SELECT * FROM reports WHERE id = ?', [reportId], (err, report) => {
        if (err) {
            console.error('检查报告错误:', err);
            return res.status(500).json({
                error: '数据库错误',
                details: err.message
            });
        }

        if (!report) {
            return res.status(404).json({ error: '报告不存在' });
        }

        // 检查权限
        if (user.role !== 'admin' && report.user_id !== user.id) {
            return res.status(403).json({ error: '无权修改此报告' });
        }

        // 数据验证
        if (!asset_name || !category_main || !unit || !value || !department || !location) {
            return res.status(400).json({
                error: '所有必填字段都是必须的',
                missingFields: {
                    asset_name: !asset_name,
                    category_main: !category_main,
                    unit: !unit,
                    value: !value,
                    department: !department,
                    location: !location
                }
            });
        }

        // 确保数值字段正确
        let valueNum = parseFloat(value);
        if (isNaN(valueNum)) {
            return res.status(400).json({ error: '资产价值必须是数字' });
        }

        // 处理图片逻辑
        let imagesToKeep = [];
        
        // 1. 处理已存在的图片
        if (existing_images) {
            imagesToKeep = existing_images.split(',').filter(img => img.trim() !== '');
        }

        // 2. 处理新上传的图片
        if (req.files && req.files.length > 0) {
            const newImages = req.files.map(file => file.filename);
            imagesToKeep = [...imagesToKeep, ...newImages];
        }

        // 3. 如果没有任何图片，保持原样
        if (imagesToKeep.length === 0 && report.images) {
            imagesToKeep = report.images.split(',');
        }

        // 构建更新数据对象
        const updateData = {
            asset_number: asset_number !== undefined ? String(asset_number) : report.asset_number,
            asset_name: String(asset_name),
            category_main: String(category_main),
            category_sub: String(category_sub || ''),
            unit: String(unit),
            value: valueNum,
            status: String(status || '正常'),
            department: String(department),
            location: String(location),
            service_life: service_life ? parseInt(service_life) : null,
            depreciation_rate: depreciation_rate ? parseFloat(depreciation_rate) : null,
            purchase_date: String(purchase_date || ''),
            manufacturer: String(manufacturer || ''),
            model: String(model || ''),
            description: String(description || ''),
            images: imagesToKeep.join(',')
        };

        db.run(`
            UPDATE reports SET
                asset_number = ?,
                asset_name = ?,
                category_main = ?,
                category_sub = ?,
                unit = ?,
                value = ?,
                status = ?,
                department = ?,
                location = ?,
                service_life = ?,
                depreciation_rate = ?,
                purchase_date = ?,
                manufacturer = ?,
                model = ?,
                description = ?,
                images = ?
            WHERE id = ?
        `, [
            updateData.asset_number,
            updateData.asset_name,
            updateData.category_main,
            updateData.category_sub,
            updateData.unit,
            updateData.value,
            updateData.status,
            updateData.department,
            updateData.location,
            updateData.service_life,
            updateData.depreciation_rate,
            updateData.purchase_date,
            updateData.manufacturer,
            updateData.model,
            updateData.description,
            updateData.images,
            reportId
        ], function (err) {
            if (err) {
                console.error('更新报告SQL错误:', err);
                
                // 如果出错，删除新上传的图片
                if (req.files && req.files.length > 0) {
                    req.files.forEach(file => {
                        fs.unlink(file.path, err => {
                            if (err) console.error('删除上传失败的图片错误:', err);
                        });
                    });
                }
                
                return res.status(500).json({
                    error: '更新报告失败',
                    details: err.message
                });
            }

            res.json({
                success: true,
                message: '报告更新成功',
                changes: this.changes,
                images: updateData.images ? updateData.images.split(',').map(img => `/uploads/${img}`) : []
            });
        });
    });
});



// 删除报告 (仅管理员)
app.delete('/api/reports/:id', requireLogin, requireAdmin, (req, res) => {
    const reportId = req.params.id;

    // 先获取图片路径以便删除文件
    db.get('SELECT images FROM reports WHERE id = ?', [reportId], (err, report) => {
        if (err) {
            console.error('查询报告图片信息错误:', err);
            return res.status(500).json({ error: '数据库错误', details: err.message });
        }
        
        if (!report) {
            return res.status(404).json({ error: '记录不存在' });
        }

        // 先删除数据库记录
        db.run('DELETE FROM reports WHERE id = ?', [reportId], function (err) {
            if (err) {
                console.error('删除报告记录错误:', err);
                return res.status(500).json({ error: '数据库错误', details: err.message });
            }

            // 如果有图片关联，删除对应的图片文件
            if (report.images && report.images.trim() !== '') {
                const images = report.images.split(',');
                
                // 遍历所有图片并删除
                images.forEach(image => {
                    const imagePath = path.join(__dirname, 'uploads', image.trim());
                    
                    // 检查文件是否存在后再删除
                    fs.access(imagePath, fs.constants.F_OK, (accessErr) => {
                        if (!accessErr) {
                            // 文件存在，执行删除
                            fs.unlink(imagePath, (unlinkErr) => {
                                if (unlinkErr) {
                                    console.error(`删除图片失败 (${imagePath}):`, unlinkErr);
                                } else {
                                    console.log(`图片已删除: ${imagePath}`);
                                }
                            });
                        } else {
                            console.warn(`图片文件不存在，跳过删除: ${imagePath}`);
                        }
                    });
                });
            }

            res.json({ 
                success: true, 
                message: '报告及关联图片已成功删除',
                deletedRows: this.changes
            });
        });
    });
});
/* ==============================================
   服务器启动
   ============================================== */
app.listen(port, () => {
    console.log(`资产管理系统运行在 http://localhost:${port}`);
});